package com.luna.dao;

import com.luna.beans.Note;
import com.luna.utils.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoteDao {
    public Note selectByNoteId(int note_id) throws Exception {
        Note note = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection con = null;
        String sql = "select * from note where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, note_id);
            rs = ps.executeQuery();
            if (rs.next()) {
                note = new Note(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getInt("uploader_id"),
                        rs.getInt("book_id")
                );
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return note;
    }

    public List<Note> selectAllNote() throws Exception {
        List<Note> list = new ArrayList<Note>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from note";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Note(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getInt("uploader_id"),
                        rs.getInt("book_id")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public List<Note> selectAllNoteByUploaderId(int uploader_id) throws Exception {
        List<Note> list = new ArrayList<Note>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from note where uploader_id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, uploader_id);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Note(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getInt("uploader_id"),
                        rs.getInt("book_id")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public List<Note> selectAllNoteByBookId(int book_id) throws Exception {
        List<Note> list = new ArrayList<Note>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from note where book_id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, book_id);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Note(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getInt("uploader_id"),
                        rs.getInt("book_id")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public void insertNote(Note note) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "insert into note(id, title, content, book_id, uploader_id) value(NULL, ?, ?, ?, ?)";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, note.getTitle());
            ps.setString(2, note.getContent());
            ps.setInt(3, note.getBook_id());
            ps.setInt(4, note.getUploader_id());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void dropNoteById(int note_id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "delete from note where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, note_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void updateNote(int id, Note note) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "update note set title=?, content=? where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, note.getTitle());
            ps.setString(2, note.getContent());
            ps.setInt(3, id);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }
}
